const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const dotenv = require('dotenv');

dotenv.config();

// 创建数据库连接
const dbPath = path.resolve(__dirname, '../database/protocol_generator.db');
const db = new sqlite3.Database(dbPath, (err) => {
  if (err) {
    console.error('数据库连接失败:', err.message);
  } else {
    console.log('已连接到SQLite数据库');
    // 初始化数据库表
    initDatabase();
  }
});

// 初始化数据库表
function initDatabase() {
  // 用户表
  db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP,
    is_admin INTEGER DEFAULT 0
  )`);

  // 协议分类表
  db.run(`CREATE TABLE IF NOT EXISTS protocol_categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    user_id INTEGER,
    is_public INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
  )`);

  // 协议表
  db.run(`CREATE TABLE IF NOT EXISTS protocols (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    user_id INTEGER NOT NULL,
    category_id INTEGER,
    is_public INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES protocol_categories(id) ON DELETE SET NULL
  )`);

  // 协议元素表
  db.run(`CREATE TABLE IF NOT EXISTS protocol_elements (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    protocol_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    en_name TEXT NOT NULL,
    element_type TEXT NOT NULL,
    position INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (protocol_id) REFERENCES protocols(id) ON DELETE CASCADE
  )`);

  // 固定类型元素属性表
  db.run(`CREATE TABLE IF NOT EXISTS fixed_element_properties (
    element_id INTEGER PRIMARY KEY,
    value TEXT NOT NULL,
    FOREIGN KEY (element_id) REFERENCES protocol_elements(id) ON DELETE CASCADE
  )`);

  // 常规类型元素属性表
  db.run(`CREATE TABLE IF NOT EXISTS normal_element_properties (
    element_id INTEGER PRIMARY KEY,
    data_type TEXT NOT NULL,
    FOREIGN KEY (element_id) REFERENCES protocol_elements(id) ON DELETE CASCADE
  )`);

  // 自动计算类型元素属性表
  db.run(`CREATE TABLE IF NOT EXISTS auto_element_properties (
    element_id INTEGER PRIMARY KEY,
    calc_type TEXT NOT NULL,
    FOREIGN KEY (element_id) REFERENCES protocol_elements(id) ON DELETE CASCADE
  )`);

  // 元素库表
  db.run(`CREATE TABLE IF NOT EXISTS element_library (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    en_name TEXT NOT NULL,
    element_type TEXT NOT NULL,
    category TEXT NOT NULL,
    user_id INTEGER,
    is_system INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
  )`);

  // 元素库属性表
  db.run(`CREATE TABLE IF NOT EXISTS element_library_properties (
    library_id INTEGER NOT NULL,
    property_name TEXT NOT NULL,
    property_value TEXT NOT NULL,
    PRIMARY KEY (library_id, property_name),
    FOREIGN KEY (library_id) REFERENCES element_library(id) ON DELETE CASCADE
  )`);

  // 用户会话表
  db.run(`CREATE TABLE IF NOT EXISTS sessions (
    id TEXT PRIMARY KEY,
    user_id INTEGER NOT NULL,
    ip_address TEXT,
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
  )`);

  console.log('数据库表初始化完成');
}

// 测试数据库连接
async function testConnection() {
  return new Promise((resolve, reject) => {
    db.get("SELECT 1", (err) => {
      if (err) {
        console.error('数据库连接测试失败:', err.message);
        resolve(false);
      } else {
        console.log('数据库连接测试成功');
        resolve(true);
      }
    });
  });
}

// 执行SQL查询（Promise包装）
function run(sql, params = []) {
  return new Promise((resolve, reject) => {
    db.run(sql, params, function(err) {
      if (err) {
        console.error('SQL执行错误:', err.message);
        reject(err);
      } else {
        resolve({ id: this.lastID, changes: this.changes });
      }
    });
  });
}

// 执行SQL查询并返回所有结果
function all(sql, params = []) {
  return new Promise((resolve, reject) => {
    db.all(sql, params, (err, rows) => {
      if (err) {
        console.error('SQL查询错误:', err.message);
        reject(err);
      } else {
        resolve(rows);
      }
    });
  });
}

// 执行SQL查询并返回第一个结果
function get(sql, params = []) {
  return new Promise((resolve, reject) => {
    db.get(sql, params, (err, row) => {
      if (err) {
        console.error('SQL查询错误:', err.message);
        reject(err);
      } else {
        resolve(row);
      }
    });
  });
}

module.exports = {
  db,
  testConnection,
  run,
  all,
  get
};
